<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;

use App\Http\Requests;
use App\Http\Controllers\Controller;
use DB;

class QuestionController extends Controller
{
    // 交流页面主页
    // 问题的详情页面
    public function getQdetail(Request $request)
    {
        // 获取问题的ID
        $id = $request->input('id');

        // 修改文章的浏览量
        $res = DB::table('card')->where('id',$id)->increment('liulan');
        // dd($res);        
        // 获取问题的详细信息
        $card = DB::table('card')
            ->join('user','card.uid','=','user.id')
            ->select('card.*','user.username','user.profile as uprofile')
            ->where('card.id',$id)->first();
        // 获取该问题下的所有回复  回复根据ID排序
        $reply = DB::table('reply')
            ->join('user','reply.u_id','=','user.id')
            ->select('reply.*','user.username','user.profile')
            ->where('reply.tid',$id)->get();
        $replys = $this->replys($reply);
        // dd($replys);
        return view('home.question.Qdetail',['card'=>$card,'replys'=>$replys,'request'=>$request]);
    }

    // 帖子的回复按照被赞次数排序 
    public function getZan(Request $request)
    {
        // 获取问题的ID
        $id = $request->input('id');
        // 获取问题的详细信息
        $card = DB::table('card')
            ->join('user','card.uid','=','user.id')
            ->select('card.*','user.username','user.profile as uprofile')
            ->where('card.id',$id)->first();
        // 获取该问题下的所有回复  回复根据回复时间排序
        $reply = DB::table('reply')
            ->join('user','reply.u_id','=','user.id')

            ->select('reply.*','user.username','user.profile')
            ->orderBy('reply.rtime','desc')
            ->where('reply.tid',$id)->get();
        $replys = $this->replys($reply);
        // dd($replys);
        return view('home.question.Qdetail',['card'=>$card,'replys'=>$replys,'request'=>$request]);
    }
    // 筛选出关注的人的回复
    public function getGuanzhu(Request $request)
    {
        // 获取问题的ID
        $id = $request->input('id');
        // 获取问题的详细信息
        $card = DB::table('card')
            ->join('user','card.uid','=','user.id')
            ->select('card.*','user.username','user.profile as uprofile')
            ->where('card.id',$id)->first();
        // 获取该问题下的所有回复 
        // 获取浏览者所关注的用户的ID
        $arr = [];
        if(session('id')){
            $users = DB::table('user_guanzhu')->where('zid',session('id'))->get();
            // dd($users);
            foreach($users as $k=>$v)
            {
                $arr[] = $v->bid;
            }
            // dd($arr);
        }



        //  回复根据回复时间排序
        $reply = DB::table('reply')
            ->join('user','reply.u_id','=','user.id')
            ->select('reply.*','user.username','user.profile')
            ->orderBy('reply.rtime','desc')
            ->where('reply.tid',$id)
            ->whereIn('u_id',$arr)->get();
        $replys = $this->replys($reply);
        // dd($replys);
        return view('home.question.Qdetail',['card'=>$card,'replys'=>$replys,'request'=>$request]);
    }

    // 帖子的回复按照时间排序 
    public function getTime(Request $request)
    {
        // 获取问题的ID
        $id = $request->input('id');
        // 获取问题的详细信息
        $card = DB::table('card')
            ->join('user','card.uid','=','user.id')
            ->select('card.*','user.username','user.profile as uprofile')
            ->where('card.id',$id)->first();
        // 获取该问题下的所有回复  回复根据回复时间排序
        $reply = DB::table('reply')
            ->join('user','reply.u_id','=','user.id')
            ->select('reply.*','user.username','user.profile')
            ->orderBy('reply.rtime','desc')
            ->where('reply.tid',$id)->get();
        $replys = $this->replys($reply);
        // dd($replys);
        return view('home.question.Qdetail',['card'=>$card,'replys'=>$replys,'request'=>$request]);
    }


    // 帖子按照ID排序
    public function getList(Request $request)
    {
        // 获取所有的帖子的信息
        $cards = DB::table('card')
            // 联合用户表,获取发帖人的信息
            ->join('user','card.uid','=','user.id')
            ->select('card.*','user.username','user.profile')
            // ->orderBy('time','desc')
            ->get();
        // 向$cards里添加count元素
        foreach($cards as $k=>$v){
            $v->rcount = DB::table('reply')->where('tid',$v->id)->count();
            $v->ccount = DB::table('card_guanzhu')->where('cid',$v->id)->count();
        }
        // 获取回复最多的帖子的信息
        $max = $this->max();
        // 获取被关注数量最多的用户的信息
        $user = $this->hotpeople()[0];
        // dd($cards);

        return view('home.question.question',['cards'=>$cards,'max'=>$max,'user'=>$user,'request'=>$request]);
    }
    // 帖子按照回复时间排序
    public function getNew(Request $request)
    {
        // 获取所有的帖子的信息
        $cards = DB::table('card')
            // 联合用户表,获取发帖人的信息
            ->join('user','card.uid','=','user.id')
            ->select('card.*','user.username','user.profile')
            // 按时间排序,获取最近的两个
            ->orderBy('time','desc')->take(2)->get();
        // 向$cards里添加count元素,获取每个帖子的回复数量
        foreach($cards as $k=>$v){
            $v->rcount = DB::table('reply')->where('tid',$v->id)->count();
            $v->ccount = DB::table('card_guanzhu')->where('cid',$v->id)->count();
        }
        // dd($cards);
        // 获取回复最多的帖子的信息
        $max = $this->max();
        // 获取被关注数量最多的用户的信息
        $user = $this->hotpeople()[0];

        return view('home.question.question',['cards'=>$cards,'max'=>$max,'user'=>$user,'request'=>$request]);
    }

    public function getUnresponsive(Request $request)
    {
        // 根据回复表获取有回复的帖子的ID
        $res = DB::table('reply')->select('tid')->get();
        $arr = [];
        $brr = [];
        // 获取帖子ID
        foreach($res as $k=>$v){
            $arr[] = $v->tid;
        }
        // 去重
        $brr = array_unique($arr);
        // dd($brr);
        // 获取不在$brr里(即没有回复)的帖子的信息
        $cards = DB::table('card')
            ->join('user','card.uid','=','user.id')
            ->select('card.*','user.username','user.profile')
            ->whereNotIn('card.id',$brr)->get();
        foreach($cards as $k=>$v){
            $v->rcount = DB::table('reply')->where('tid',$v->id)->count();
            $v->ccount = DB::table('card_guanzhu')->where('cid',$v->id)->count();
        }

        // 获取回复最多的帖子的信息
        $max = $this->max();
        // 获取被关注数量最多的用户的信息
        $user = $this->hotpeople()[0];

        return view('home.question.unresponsive',['cards'=>$cards,'max'=>$max,'user'=>$user,'request'=>$request]);
    }


    // 提取常用功能    获取回复最多的帖子的信息
    public function max()
    {
        $reply = DB::table('reply')->groupBy('tid')
            ->select(DB::raw('tid, count(tid) as total'))
            ->orderBy('total','desc')->first();
        $tid = $reply->tid;
        // dd($tid);
        $max = DB::table('card')->where('id',$tid)->first();
        $max->count = DB::table('reply')->where('tid',$tid)->count();
        // dd($max);
        return $max;
    }

    // 获取被关注人数最多的用户信息
    public function getHotpeople()
    {
        $users = $this->hotpeople();
        // dd($users);
        // return view('home.user.hotpeople',['users'=>$users,'request'=>$request]);
    }
    public function hotpeople()
    {
        $users = DB::table('user_guanzhu')->groupBy('bid')
            ->join('user','user_guanzhu.bid','=','user.id')
            ->select('user.*',DB::raw('bid, count(bid) as total'))
            ->orderBy('total','desc')->get();
        // 为用户添加发表帖子的数据
        foreach($users as $k=>$v){
            $v->rcount = DB::table('reply')->where('u_id',$v->id)->count();
            $v->zcount = (DB::table('reply_zan')->where('uid',$v->id)->count())+(DB::table('card_guanzhu')->where('uid',$v->id)->count());
        }
        // dd($users);
        return $users;
    }

    // 获取每个回复的附加信息
    public function replys($replys)
    {
        foreach($replys as $k=>$v){
            if(($v->pid != 0)){
                $p = DB::table('reply')->select('u_id')->where('rid',$v->pid)->first();
                $a = $p->u_id;
                // dd($a);
                $user = DB::table('user')->select('username')->where('id',$a)->first();
                // dd($user);
                $pname = $user->username;
                // dd($username);
                $v->pname = $pname;
            }else{
                $v->pname = '';
            }
            $v->zcount = DB::table('reply_zan')->where('rid',$v->rid)->count();
        }

        return $replys;
    }

}
